﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.UserDesigner;

namespace BHair.Base
{
    public partial class frmjccz : Form
    {
        public frmjccz()
        {
            InitializeComponent();
        }
        string siweils = "";
        string constr = ConfigurationManager.ConnectionStrings["BHair.Properties.Settings.BHairDBConnectionString"].ConnectionString;
        string cardid = "";
        int jibieid;
        decimal jf_kcjf = 0;    //扣除积分
        decimal jf_zjjf = 0;    //增加积分
        decimal jf_dqjf = 0;    //当前积分
        private void frmjccz_Load(object sender, EventArgs e)
        {
            this.button3.Visible = false;
            /*
            string bs = "CC-";
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            Random ran = new Random();
            int RandKey = ran.Next(10, 999);
            string djh = bs + time + "-" + RandKey.ToString();
            this.txtdjh.Text = djh;
            */
            txtdjje.Text = "0";
            txtyhje.Text = "0";
            txtysje.Text = "0";
            txtssje.Text = "0";

            djh();

            jcxm();
            jzzh();
        }
        //加载收款账户】
        public void jzzh()
        {
            SqlConnection conn = new SqlConnection(constr);
            try
            {
                conn.Open();
                string sql = "SELECT [zhID],[zhname] FROM [BHairDB].[dbo].[yhzh]";
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                sda.Fill(ds, "yhzh");
                comboBox1.DataSource = ds.Tables["yhzh"];
                comboBox1.DisplayMember = "zhname";
            }
            catch (Exception ee)
            {
                conn.Close();
                MessageBox.Show(ee.Message);
            }
        }
        //计算合计金额
        public void Total(DataGridView dg)
        {
            // DataGridViewRow dgr = dg.Rows[dg.Rows.Count];
            decimal sum = 0;
            for (int i = 0; i < dg.Rows.Count; i++)
            {
                sum += Convert.ToDecimal(Convert.ToSingle(dg.Rows[i].Cells[5].Value));
            }
            this.txtdjje.Text = sum.ToString();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if (txtcardID.ReadOnly == true)
            {
                txtcardID.ReadOnly = false;
            }
            else
            {

                if (txtcardID.Text == string.Empty)
                {
                    MessageBox.Show("请输入卡号！");
                }
                else
                {
                    SqlConnection connc = new SqlConnection(constr);
                    connc.Open();
                    string cardsql = string.Format("SELECT mid FROM member where mid='{0}' or mphone='{1}'", txtcardID.Text, txtcardID.Text);
                    SqlCommand cardcomm = new SqlCommand(cardsql, connc);
                    SqlDataReader sdrcard = cardcomm.ExecuteReader();
                    while (sdrcard.Read())
                    {
                        cardid = sdrcard["mid"].ToString();
                    }
                    chuanzhi ca = new chuanzhi();
                    if (ca.CheckPass(cardid) == true)
                    {
                        if (ca.Checkyxq(cardid) == true)
                        {
                            this.button3.Visible = true;
                            this.txtcardID.ReadOnly = true;
                            cfxx();
                        }
                        else
                        {
                            MessageBox.Show("此卡已过期");
                            txtcardID.Text = null;
                        }
                    }
                    else
                    {
                        MessageBox.Show("卡号不存在！");
                        txtcardID.Text = null;
                    }
                }
            }

        }
        //查询持卡人信息
        public void cfxx()
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql = string.Format("SELECT [mname],[mremark],[mbalance],积分,会员级别,累计积分 FROM [member] where mid='{0}' or mphone='{1}'", txtcardID.Text, txtcardID.Text);
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sdr = comm.ExecuteReader();
            while (sdr.Read())
            {
                lbckr.Text = sdr["mname"].ToString();
                lbye.Text = sdr["mbalance"].ToString();
                lbhybz.Text = sdr["mremark"].ToString();
                lbdqjf.Text = sdr["积分"].ToString();
                lbljjf.Text = sdr["累计积分"].ToString();
                if (sdr["会员级别"].ToString() != string.Empty)
                {
                    lbjb.Text = sdr["会员级别"].ToString();
                    SqlConnection conn1 = new SqlConnection(constr);
                    conn1.Open();
                    
                    string sql1 =string.Format( "SELECT [id] ,[级别],[积分额度] ,[积分率],[倍数] FROM [会员级别] where 级别='{0}'", sdr["会员级别"].ToString());
                    SqlCommand comm1 = new SqlCommand(sql1,conn1);
                    SqlDataReader sdr1 = comm1.ExecuteReader();
                    while (sdr1.Read())
                    {
                        jibieid =Int32.Parse( sdr1["id"].ToString());
                    }
                    //jibieid=sdr[]
                }
                else
                {
                    lbjb.Text = "此卡不享受积分";
                    jibieid = 1;
                }
            }
           
            conn.Close();
            积分率();
        }
        public void innertjifen()
        {
            //用应收金额 除积分率  等于本次积分
            decimal ys ;
            decimal jifenlv ;
            decimal ccjf ;
            decimal bencijifen ;
            if (cbjf.Checked == true)
            {
                ys = decimal.Parse(txtysje.Text);
                jifenlv = decimal.Parse(lbjfl.Text);
                 ccjf = ys / jifenlv* decimal.Parse(lbbeilv.Text);
                 bencijifen = (ys / jifenlv) * decimal.Parse(lbbeilv.Text) + syjf;
            }
            else
            {
                 ys = decimal.Parse(txtysje.Text);
              jifenlv = decimal.Parse(lbjfl.Text);
                 ccjf = ys / jifenlv* decimal.Parse(lbbeilv.Text);
                 bencijifen = (ys / jifenlv) * decimal.Parse(lbbeilv.Text) + decimal.Parse(lbdqjf.Text);
            }
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string str = string.Format("INSERT INTO [积分表]([卡号] ,[持卡人] ,[级别] ,[本次积分] ,[总积分] ,[消费单号] ,[消费日期],类型) VALUES('{0}' ,'{1}' ,'{2}','{3}' ,'{4}' ,'{5}' ,'{6}','增加')", txtcardID.Text, lbckr.Text, lbjb.Text, ccjf, bencijifen, txtdjh.Text, DateTime.Now.ToString("yyyy-MM-dd"));
            SqlCommand comm = new SqlCommand(str, conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        //查询卡积分率
        public void 积分率()
        {
            //首先查询会员卡分类
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql = string.Format("SELECT [会员级别] FROM [member] where mid='{0}'", txtcardID.Text);
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataReader sdr = comm.ExecuteReader();
            string kafenlei = "";
            while (sdr.Read())
            {
                kafenlei = sdr["会员级别"].ToString();
            }
            sdr.Close();
            conn.Close();
            //根据会员级别获取 卡的积分率
            SqlConnection conn1 = new SqlConnection(constr);
            conn1.Open();
            string sql1 = string.Format("SELECT 积分率,倍数 FROM [会员级别] where 级别='{0}'", kafenlei);
            SqlCommand comm1 = new SqlCommand(sql1, conn1);
            SqlDataReader sdr1 = comm1.ExecuteReader();
            decimal jfl = decimal.Parse("0.00");
            while (sdr1.Read())
            {
                jfl = decimal.Parse(sdr1["积分率"].ToString());
                lbbeilv.Text =sdr1["倍数"].ToString();
            }
            lbjfl.Text = jfl.ToString();

        }
        //查询所有计次项目
        public void jcxm()
        {
            SqlConnection conn = new SqlConnection(constr);
            try
            {
                conn.Open();
                string sql = "SELECT [iid] as 商品编号 ,[iname] as 项目名称,inprice 项目单价 FROM [BHairDB].[dbo].[items] where jc=1 order by cy desc";
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                this.dataGridView1.DataSource = dt;
                conn.Close();
            }
            catch (Exception ee)
            {
                conn.Close();
                MessageBox.Show(ee.Message);
            }
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (txtcardID.Text != string.Empty)
                {
                    txtxmname.Text = dataGridView1.SelectedCells[1].Value.ToString();
                    txtdj.Text = dataGridView1.SelectedCells[2].Value.ToString();
                    lbxmid.Text = dataGridView1.SelectedCells[0].Value.ToString();
                    //查询剩余次数
                    //首先判断数据库里面有没有数据
                    SqlConnection conn = new SqlConnection(constr);
                    conn.Open();
                    string sql = string.Format("SELECT jcspid ,jcxmname,jcnum FROM csmx  where cardID={0} and jcspid='{1}' and 备注=''", txtcardID.Text, dataGridView1.SelectedCells[0].Value);
                    string sql1 = string.Format("SELECT count(*) FROM csmx  where cardID={0} and jcspid='{1}'", txtcardID.Text, dataGridView1.SelectedCells[0].Value);
                    int rows = 0;
                    SqlConnection conn1 = new SqlConnection(constr);
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand(sql1, conn1);
                    rows = Int32.Parse(comm1.ExecuteScalar().ToString());

                    if (rows > 0)
                    {
                        SqlCommand comm = new SqlCommand(sql, conn);
                        SqlDataReader sdr = comm.ExecuteReader();
                        while (sdr.Read())
                        {

                            txtyc.Text = sdr["jcnum"].ToString();

                        }
                    }
                    else
                    {
                        txtyc.Text = "0";
                    }
                    conn.Close();
                    conn1.Close();
                }
                else
                {
                    MessageBox.Show("请先刷卡");
                }
            }
            catch { }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            int index = this.dataGridView2.Rows.Add();
            this.dataGridView2.Rows[index].Cells[0].Value = lbxmid.Text;
            this.dataGridView2.Rows[index].Cells[1].Value = txtxmname.Text;
            this.dataGridView2.Rows[index].Cells[3].Value = txtdj.Text;
            this.dataGridView2.Rows[index].Cells[4].Value = txtcs.Text;
            this.dataGridView2.Rows[index].Cells[2].Value = txtyc.Text;
            this.dataGridView2.Rows[index].Cells[5].Value = decimal.Parse(txtdj.Text) * decimal.Parse(txtcs.Text);
            DataGridView dgv = dataGridView2;
            Total(dgv);
            txtysje.Text = (decimal.Parse(txtdjje.Text) - decimal.Parse(txtyhje.Text)).ToString();
        }

        private void txtyhje_TextChanged(object sender, EventArgs e)
        {
            try
            {
                txtysje.Text = (decimal.Parse(txtdjje.Text) - decimal.Parse(txtyhje.Text)).ToString();
            }
            catch { }
        }

        private void txtssje_TextChanged(object sender, EventArgs e)
        {
            try
            {
                txtzl.Text = (decimal.Parse(txtssje.Text) - decimal.Parse(txtysje.Text)).ToString();
            }
            catch { }
        }
        //插入收入明细表
        public void insertsrmx()
        {
            string sql2 = string.Format("INSERT INTO [BHairDB].[dbo].[收入明细]([djh],[skje],[zsje],[djje],[yhje],积分抵扣,[skfs],[djbz],[jsr],[djrq],[班组])VALUES('{0}',{1},'{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}')", txtdjh.Text, txtysje.Text, 0, txtdjje.Text, txtyhje.Text,txtdkje.Text, comboBox1.Text, "项目充次卡号：" + txtcardID.Text + "姓名：" + lbckr.Text, chuanzhi.userID, DateTime.Now.ToString("yyyy-MM-dd"), chuanzhi.banzu);
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql2, conn);
            int ifnum = comm.ExecuteNonQuery();
            if (ifnum <= 0)
            {
                MessageBox.Show("充值成功，但收入明细表插入失败请联系软件经销商单据号为" + txtdjh.Text);
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            decimal ssje = decimal.Parse(txtssje.Text);
            decimal ysje = decimal.Parse(txtysje.Text);
            if (txtcardID.Text != string.Empty)
            {
                if (ssje < ysje)
                {
                    MessageBox.Show("实收金额小于应收金额，请检查");
                }
                else
                {
                    if (checkBox1.Checked == false)//未选中为正常充值
                    {
                        if (cbjf.Checked == true)
                        {
                            扣除积分();
                            jifen_leiji();
                        }

                        if (lbjb.Text != "此卡不享受积分" && cbbujifen.Checked == false)

                        {
                            jifen();
                            jifen_leiji();
                            innertjifen();
                            panduanjibie();

                        } else if (cbbujifen.Checked == false)
                        {
                            jifen_leiji();
                            panduanjibie();

                        } else if (cbbujifen.Checked == true)
                        {
                            jifen_leiji();
                            panduanjibie(); 
                        }

                        insertjccz();
                        insertxmczmx();
                        insertsrmx();
                        updatexm();

                    }
                    else if (checkBox1.Checked == true)//选中为项目预存
                    {
                        if (cbjf.Checked == true)
                        {
                            扣除积分();
                            jifen_leiji();
                        }

                        if (lbjb.Text != "此卡不享受积分" && cbbujifen.Checked == false)
                        {
                            jifen();
                            jifen_leiji();
                            innertjifen();

                            panduanjibie();

                        } else if (cbbujifen.Checked==false)
                        {
                            jifen_leiji();
                            panduanjibie();
                           
                        }
                        insertjccz();
                        insertxmczmx();
                        insertsrmx();
                        ycxm();

                    }
                }
            }
            else
            {
                MessageBox.Show("请输入卡号");
            }
        }
        decimal syjf =decimal.Parse( "0.00");
        public void 扣除积分()
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            //插入积分表
            //用应收金额 除积分率  等于本次积分
           
            string str = string.Format("INSERT INTO [积分表]([卡号] ,[持卡人] ,[级别] ,[本次积分] ,[总积分] ,[消费单号] ,[消费日期],类型) VALUES('{0}' ,'{1}' ,'{2}','{3}' ,'{4}' ,'{5}' ,'{6}','扣除')", txtcardID.Text, lbckr.Text, lbjb.Text, txtkcjf.Text, lbdqjf.Text, txtdjh.Text, DateTime.Now.ToString("yyyy-MM-dd"));
            SqlCommand comm = new SqlCommand(str,conn);
            comm.ExecuteNonQuery();
            syjf = decimal.Parse(lbdqjf.Text) - decimal.Parse(txtkcjf.Text);
            string sql =string.Format( "UPDATE [member] SET[积分] ='{0}'  WHERE mid='{1}'",syjf,txtcardID.Text);
            SqlCommand comm1 = new SqlCommand(sql,conn);
            comm1.ExecuteNonQuery();
            conn.Close();
        }
        public void jifen_leiji()
        {
            decimal bencujifen = decimal.Parse("0.00");
            decimal ys = decimal.Parse(txtysje.Text);
            decimal jifenlv = decimal.Parse(lbjfl.Text);
            bencujifen = (ys / jifenlv) * decimal.Parse(lbbeilv.Text) + decimal.Parse(lbljjf.Text);
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql = string.Format("UPDATE [member] SET [累计积分] = '{0}'  WHERE mid='{1}'", bencujifen, txtcardID.Text);
            SqlCommand comm = new SqlCommand(sql,conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        public void jifen()
        {
            //用应收金额 除积分率 *倍数 等于本次积分
            decimal bencujifen =decimal.Parse( "0.00");
            if (cbjf.Checked == false)
            {
                decimal ys = decimal.Parse(txtysje.Text.ToString().Trim());
                decimal jifenlv = decimal.Parse(lbjfl.Text);
                bencujifen = (ys / jifenlv)*decimal.Parse( lbbeilv.Text)+ decimal.Parse(lbdqjf.Text);
            }
            else
            {
                decimal ys = decimal.Parse(txtysje.Text);
                decimal jifenlv = decimal.Parse(lbjfl.Text);
                bencujifen = (ys / jifenlv)*decimal.Parse(lbbeilv.Text) +decimal.Parse( syjf.ToString());
            }
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql =string.Format( "UPDATE [member] SET [积分] = '{0}'  WHERE mid='{1}'", bencujifen, txtcardID.Text);
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        public void panduanjibie()
        {
            try
            {
                //查询会员积分
                string sql = string.Format("SELECT [累计积分] FROM [member]where mid='{0}'", txtcardID.Text);
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataReader sdr = comm.ExecuteReader();
                decimal dqjf = decimal.Parse("0.00");
                while (sdr.Read())
                {
                    dqjf = decimal.Parse(sdr["累计积分"].ToString());
                }
                //根据积分判断是否符合额度要求
                //查询各个级别 积分额度
                sdr.Close();
                conn.Close();
                SqlConnection conn1 = new SqlConnection(constr);
                conn1.Open();
                string sql1 = string.Format("SELECT [id] ,[级别] ,[积分额度] ,[积分率]FROM[会员级别] where 积分额度<={0} order by 积分额度 desc", dqjf);
                SqlCommand comm1 = new SqlCommand(sql1, conn1);
                int idd = Int32.Parse(comm1.ExecuteScalar().ToString()); //获取级别ID
                if (idd > jibieid)
                {                                             //根据级别id获取级别
                    string sql2 = string.Format("SELECT 级别 FROM [会员级别] where id={0}", idd);
                    SqlCommand comm2 = new SqlCommand(sql2, conn1);
                    SqlDataReader sdr2 = comm2.ExecuteReader();
                    string jibie = "";
                    while (sdr2.Read())
                    {
                        jibie = sdr2["级别"].ToString();
                    }
                    //修改当前卡级别
                    sdr2.Close();
                    conn1.Close();
                    string sql3 = string.Format("UPDATE [member] SET [会员级别] = '{0}' WHERE mid='{1}'", jibie, txtcardID.Text);
                    SqlConnection conn3 = new SqlConnection(constr);
                    conn3.Open();
                    SqlCommand com3 = new SqlCommand(sql3, conn3);
                    com3.ExecuteNonQuery();
                    conn.Close();
                    ///
                    /// 20181201 
                    /// 更新 
                    /// 新增不享受积分卡的级别如果累计积分满足升级，那么本次消费产生的积分-升级的额度=本次的可用积分
                    ///
                    if (lbjb.Text == "此卡不享受积分")
                    {
                        SqlConnection con1 = new SqlConnection(constr);
                        con1.Open();
                        string sql_1 = string.Format("select [积分] from member where mid='{0}'", txtcardID.Text);
                        SqlCommand com1 = new SqlCommand(sql_1,con1);
                        SqlDataReader sdr_1 = com1.ExecuteReader();
                        decimal jifen_jy=0;
                        while (sdr_1.Read())
                        {
                            jifen_jy =decimal.Parse( sdr_1["积分"].ToString());
                        }

                        ///////
                       decimal jifen_keyong=dqjf - 100+jifen_jy;
                        SqlConnection con_keyong = new SqlConnection(constr);
                        con_keyong.Open();
                        string sql_ky =string.Format( "update member set [积分]='{0}' where mid='{1}'",jifen_keyong, txtcardID.Text);
                        SqlCommand com_ky = new SqlCommand(sql_ky, con_keyong);
                        com_ky.ExecuteNonQuery();
                        con_keyong.Close();
                        SqlConnection con_in = new SqlConnection(constr);
                        con_in.Open();
                        string str = string.Format("INSERT INTO [积分表]([卡号] ,[持卡人] ,[级别] ,[本次积分] ,[总积分] ,[消费单号] ,[消费日期],类型) VALUES('{0}' ,'{1}' ,'{2}','{3}' ,'{4}' ,'{5}' ,'{6}','转正增加')", txtcardID.Text, lbckr.Text, lbjb.Text, jifen_keyong, jifen_keyong, txtdjh.Text, DateTime.Now.ToString("yyyy-MM-dd"));
                        SqlCommand com_in = new SqlCommand(str,con_in);
                        com_in.ExecuteNonQuery();
                        con_in.Close();
                    }

                    MessageBox.Show("此卡积分已够" + dqjf + "已自动升级级别" + jibie);
                }
            }
            catch { }

        }
        //插入计次充值表
        public void insertjccz()
        {
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            string sql =string.Format( "INSERT INTO [计次充值]([单号],[卡号],[持卡人],[时间] ,[单据金额] ,[优惠金额] ,[应收金额],[实收金额],[找零],[经手人] ,[收款账户],[班组],if1) VALUES('{0}' ,'{1}' ,'{2}' ,'{3}','{4}' ,'{5}' ,'{6}','{7}' ,'{8}' ,'{9}' ,'{10}','{11}',1)",txtdjh.Text,txtcardID.Text,lbckr.Text,time,txtdjje.Text,txtyhje.Text,txtysje.Text,txtssje.Text,txtzl.Text,chuanzhi.userID,comboBox1.Text,chuanzhi.banzu);
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql,conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        //插入项目充值明细表
        public void insertxmczmx()
        {
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            int rowcount = this.dataGridView2.Rows.Count;
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            for (int i = 0; i < rowcount; i++)
            {
                string sql = string.Format("INSERT INTO [BHairDB].[dbo].[项目充值明细]([cardid] ,[持卡人],[项目名称],[充值次数] ,[项目单价] ,[项目金额],[经手人] ,[充值时间] ,[单号],[备注],if1) VALUES  ('{0}' ,'{1}' ,'{2}' ,{3} ,{4} ,{5} ,'{6}' ,'{7}' ,'{8}','{9}',1)", txtcardID.Text, lbckr.Text, dataGridView2.Rows[i].Cells[1].Value, dataGridView2.Rows[i].Cells[4].Value, dataGridView2.Rows[i].Cells[3].Value, dataGridView2.Rows[i].Cells[5].Value,chuanzhi.userID,time,txtdjh.Text, dataGridView2.Rows[i].Cells[6].Value);
                SqlCommand comm = new SqlCommand(sql,conn);
                comm.ExecuteNonQuery();
            }
            conn.Close();
        }
        //修改卡号所对应的项目次数
        public void updatexm()
        {
            decimal cs = 0; 
            SqlConnection conn = new SqlConnection(constr);
            SqlConnection conn1 = new SqlConnection(constr);
            SqlConnection conn2 = new SqlConnection(constr);
            int rowcount = this.dataGridView2.Rows.Count;
            int rows = 0;
            for (int i = 0; i < rowcount; i++)
            {
                conn.Open();
                string xmID = this.dataGridView2.Rows[i].Cells[0].Value.ToString();
                decimal czcs =decimal.Parse( this.dataGridView2.Rows[i].Cells[4].Value.ToString());
                string sql =string.Format("SELECT [cardID],[jcspid],[jcnum] FROM [csmx]  where cardID='{0}' and jcspid='{1}' and 备注=''", txtcardID.Text,xmID);
                string sql1 = string.Format("select COUNT(*) from csmx  where cardID= '{0}' and jcspid ='{1}' and 备注=''", txtcardID.Text, dataGridView2.Rows[i].Cells[0].Value);
                SqlCommand comm1 = new SqlCommand(sql1,conn1);
                conn1.Open();
                 rows =Int32.Parse( comm1.ExecuteScalar().ToString());
                 //string rebz = dataGridView2.Rows[i].Cells[6].Value.ToString().Trim();
                if (dataGridView2.Rows[i].Cells[6].Value != null)
                {
                    //备注不为空为推荐项目  插入而不想加 项目iD为原ID加备注
                    string sql2 = string.Format("INSERT INTO [csmx]([cardID],[jcspid] ,[jcnum],[备注],jcxmname) VALUES ('{0}','{1}',{2},'{3}','{4}')", txtcardID.Text, dataGridView2.Rows[i].Cells[0].Value.ToString(), dataGridView2.Rows[i].Cells[4].Value, dataGridView2.Rows[i].Cells[6].Value, dataGridView2.Rows[i].Cells[1].Value);
                    conn2.Open();
                    SqlCommand comm2 = new SqlCommand(sql2, conn2);
                    comm2.ExecuteNonQuery();
                }
                else
                {
                    //备注为空 正常充值
                    if (rows == 0)
                    {
                        string sql2 = string.Format("INSERT INTO [csmx]([cardID],[jcspid] ,[jcnum],[备注],jcxmname)VALUES('{0}',{1},{2},'{3}','{4}')", txtcardID.Text, dataGridView2.Rows[i].Cells[0].Value, dataGridView2.Rows[i].Cells[4].Value, dataGridView2.Rows[i].Cells[6].Value, dataGridView2.Rows[i].Cells[1].Value);
                        conn2.Open();
                        SqlCommand comm2 = new SqlCommand(sql2, conn2);
                        comm2.ExecuteNonQuery();
                    }
                    else
                    {

                        SqlCommand comm = new SqlCommand(sql, conn);
                        SqlDataReader sdr = comm.ExecuteReader();
                        while (sdr.Read())
                        {
                            cs = decimal.Parse(sdr["jcnum"].ToString());

                            decimal upcs = czcs + cs;
                            string sql3 = string.Format("UPDATE [csmx] SET [jcnum] = {0}, jcxmname='{1}' WHERE 备注='' and jcspid='{2}' and cardID='{3}'", upcs, dataGridView2.Rows[i].Cells[1].Value, xmID, txtcardID.Text);
                            SqlConnection conn3 = new SqlConnection(constr);
                            conn3.Open();
                            SqlCommand comm3 = new SqlCommand(sql3, conn3);
                            comm3.ExecuteNonQuery();
                            MessageBox.Show("充值成功");
                            conn3.Close();
                        }
                    }
                }
                conn.Close();
                conn1.Close();
                conn2.Close();
                
            }
           
            djhshuaxin();
            djh();

        }
        //打印
        public DataSet czdy()
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql =string.Format( "SELECT [计次充值].[ID]"
                + "      ,[计次充值].[单号]"
                + "      ,[计次充值].[卡号]"
                + "      ,[计次充值].[持卡人]"
                + "      ,[计次充值].[时间]"
                + "      ,[计次充值].[单据金额]"
                + "      ,[计次充值].[优惠金额]"
                + "      ,[计次充值].[应收金额]"
                + "      ,[计次充值].[实收金额]"
                + "      ,[计次充值].[找零]"
                + "      ,[计次充值].[经手人]"
                + "      ,[计次充值].[收款账户]"
                + "      ,项目充值明细.cardid"
                + "      ,项目充值明细.充值时间"
                + "      ,项目充值明细.充值次数"
                + "      ,项目充值明细.单号"
                + "      ,项目充值明细.持卡人"
                + "	  ,项目充值明细.经手人"
                + "	  ,项目充值明细.项目单价"
                + "	  ,项目充值明细.项目名称"
                + "	  ,项目充值明细.项目金额"
                + "  FROM [BHairDB].[dbo].[计次充值] join 项目充值明细 on 计次充值.单号=项目充值明细.单号 where [计次充值].[单号]='{0}'",txtdjh.Text);
            
            SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
            sda.Fill(ds, "[计次充值]");
            return ds;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            DevExpress.XtraReports.UI.XtraReport xr = new DevExpress.XtraReports.UI.XtraReport();
            xr.LoadLayout(AppDomain.CurrentDomain.BaseDirectory + @"bb\计次项目充值.repx");
            xr.DataSource = czdy();
            xr.ShowPreview();
        }
        public void djh()
        {
            string lsh = "";
            try
            {
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();
                string sql = "SELECT [jccz] FROM [djh]";
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataReader sdr = comm.ExecuteReader();
                while (sdr.Read())
                {
                    lsh = sdr["jccz"].ToString();
                }
                string bs = "CC-";
                string time = DateTime.Now.ToString("yyyy-MM-dd");
                /*
                Random ran = new Random();
                int RandKey = ran.Next(10, 999);
                */
                int djlsh = Int32.Parse(lsh) + 1;
                siweils = string.Format("{0:00000}", djlsh);
                string djh = bs + time + "-" + chuanzhi.banzu + siweils;
                this.txtdjh.Text = djh;
                conn.Close();
                txtdjje.Text = "0";
                txtyhje.Text = "0";
                txtysje.Text = "0";
                txtssje.Text = "0";
                txtcardID.Text = "";
                //dataGridView1.DataSource = null;
                DataGridView dgv = dataGridView2;
                dgv.Rows.Clear();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

        }
        public void djhshuaxin()
        {
            try
            {
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();

                string sql = string.Format("UPDATE [djh] SET [jccz] = '{0}'", siweils);
                SqlCommand comm = new SqlCommand(sql, conn);
                comm.ExecuteNonQuery();
            }
            catch { }
        }

        private void txtcardID_KeyDown(object sender, KeyEventArgs e)
        {

            if (e.KeyCode == Keys.Enter)
            {
                if (txtcardID.Text == string.Empty)
                {
                    MessageBox.Show("请输入卡号！");
                }
                else
                {
                    SqlConnection connc = new SqlConnection(constr);
                    connc.Open();
                    string cardsql = string.Format("SELECT mid FROM member where mid='{0}' or mphone='{1}'", txtcardID.Text, txtcardID.Text);
                    SqlCommand cardcomm = new SqlCommand(cardsql, connc);
                    SqlDataReader sdrcard = cardcomm.ExecuteReader();
                    while (sdrcard.Read())
                    {
                        cardid = sdrcard["mid"].ToString();
                    }
                    chuanzhi ca = new chuanzhi();
                    if (ca.CheckPass(cardid) == true)
                    {
                        if (ca.Checkyxq(cardid) == true)
                        {
                            this.button3.Visible = true;
                            this.txtcardID.ReadOnly = true;
                            cfxx();
                        }
                        else
                        {
                            MessageBox.Show("此卡已过期");
                            txtcardID.Text = null;
                        }
                    }
                    else
                    {
                        MessageBox.Show("卡号不存在！");
                        txtcardID.Text = null;
                    }
                }
            }
        }

        private void checkBox1_Click(object sender, EventArgs e)
        {
            //判断是否被选中
            if (checkBox1.Checked == true)
            {
                //如果被选中择修改单据号为YC
                string str = txtdjh.Text;
                //截取字符串前两位
                string str1 = str.Remove(0, 2);
                // MessageBox.Show(str1);
                txtdjh.Text = "YC" + str1;
            }
            else if (checkBox1.Checked == false)
            {
                string str = txtdjh.Text;
                //截取字符串前两位
                string str1 = str.Remove(0, 2);
                // MessageBox.Show(str1);
                txtdjh.Text = "CC" + str1;
            }
        }
        public void ycxm()
        {
            decimal cs = 0;
            SqlConnection conn = new SqlConnection(constr);
            SqlConnection conn1 = new SqlConnection(constr);
            SqlConnection conn2 = new SqlConnection(constr);
            int rowcount = this.dataGridView2.Rows.Count;
            int rows = 0;
            for (int i = 0; i < rowcount; i++)
            {
                conn.Open();
                string time = DateTime.Now.ToString("yyyy-MM-dd");
                string xmID = this.dataGridView2.Rows[i].Cells[0].Value.ToString();
                decimal czcs = decimal.Parse(this.dataGridView2.Rows[i].Cells[4].Value.ToString());
                string sql = string.Format("SELECT [cardID],[jcspid],[jcnum] FROM [ycxm]  where cardID='{0}' and jcspid={1}", txtcardID.Text, xmID);
                string sql1 = string.Format("select COUNT(*) from ycxm join items on ycxm.jcspid= items.iid where cardID= '{0}' and jcspid ={1}", txtcardID.Text, dataGridView2.Rows[i].Cells[0].Value);
                SqlCommand comm1 = new SqlCommand(sql1, conn1);
                conn1.Open();
                rows = Int32.Parse(comm1.ExecuteScalar().ToString());
                if (rows == 0)
                {
                    string sql2 = string.Format("INSERT INTO [ycxm]([cardID],[jcspid] ,[jcnum],date1,bz)VALUES('{0}',{1},{2},'{3}','{4}')", txtcardID.Text, dataGridView2.Rows[i].Cells[0].Value, dataGridView2.Rows[i].Cells[4].Value,time, dataGridView2.Rows[i].Cells[6].Value);
                    conn2.Open();
                    SqlCommand comm2 = new SqlCommand(sql2, conn2);
                    comm2.ExecuteNonQuery();
                }
                else
                {

                    SqlCommand comm = new SqlCommand(sql, conn);
                    SqlDataReader sdr = comm.ExecuteReader();
                    while (sdr.Read())
                    {
                        cs = decimal.Parse(sdr["jcnum"].ToString());

                        decimal upcs = czcs + cs;
                        string sql3 = string.Format("UPDATE [ycxm] SET [jcnum] = {0} WHERE jcspid={1} and cardID='{2}'", upcs, xmID, txtcardID.Text);
                        SqlConnection conn3 = new SqlConnection(constr);
                        conn3.Open();
                        SqlCommand comm3 = new SqlCommand(sql3, conn3);
                        comm3.ExecuteNonQuery();
                        conn3.Close();
                    }
                }
                conn.Close();
                conn1.Close();
                conn2.Close();

            }
            MessageBox.Show("充值成功");
            djhshuaxin();
            djh();
        }

        private void txtdjh_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                SqlConnection conn1 = new SqlConnection(constr);
                conn1.Open();
                string sql1 =string.Format( "SELECT [cardid] ,[持卡人] FROM [项目充值明细] where [项目充值明细].单号='{0}'",txtdjh.Text);
                SqlCommand comm1 = new SqlCommand(sql1,conn1);
                SqlDataReader sdr = comm1.ExecuteReader();
                while (sdr.Read())
                {
                    txtcardID.Text = sdr["cardid"].ToString();
                    lbckr.Text = sdr["持卡人"].ToString();
                }
                conn1.Close();

                string sql =string.Format( "SELECT [项目名称],[充值次数],[项目单价],[项目金额],备注 FROM [项目充值明细] where 单号='{0}'",txtdjh.Text);
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridView2.Columns.Clear();
                dataGridView2.DataSource = dt;
                conn.Close();
                chaxunxiaofei();
                button5.Visible = true;
            }
        }
        public void chaxunxiaofei()
        {
            //查询当前积分
            SqlConnection con_zjf = new SqlConnection(constr);
            con_zjf.Open();
            string sql_zjf =string.Format( "select * from member where mid='{0}'",txtcardID.Text);
            SqlCommand com_zjf = new SqlCommand(sql_zjf,con_zjf);
            SqlDataReader sdr_zjf = com_zjf.ExecuteReader();
            while (sdr_zjf.Read())
            {
                jf_dqjf = decimal.Parse(sdr_zjf["积分"].ToString());
            }


            //根据单号填充 扣除积分 本次积分
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
          
            string sql =string.Format( "SELECT * FROM [积分表] where 消费单号='{0}' and 类型='扣除'",txtdjh.Text);
            SqlCommand comm = new SqlCommand(sql,conn);
            SqlDataReader sdr = comm.ExecuteReader();
            while (sdr.Read())
            {
                jf_kcjf =decimal.Parse( sdr["本次积分"].ToString());
            }
            txtkcjf.Text = jf_kcjf.ToString();
            //查询增加积分
            conn.Close();
            SqlConnection conn1 = new SqlConnection(constr);
            conn1.Open();
            string sql_zjjf = string.Format("SELECT * FROM [积分表] where 消费单号='{0}' and 类型='增加'", txtdjh.Text);
            SqlCommand comm1 = new SqlCommand(sql_zjjf,conn1);
            SqlDataReader sdrzj = comm1.ExecuteReader();
            while (sdrzj.Read())
            {
                jf_zjjf =decimal.Parse( sdrzj["本次积分"].ToString());
            }
            conn1.Close();
        }

        private void button5_Click(object sender, EventArgs e)
        {
           
            //修改剩余次数
            //查询项目当前次数
            for (int i = 0; i < dataGridView2.Rows.Count; i++)
            {
                decimal sycs = 0;
                string time = DateTime.Now.ToString("yyyy-MM-dd");
                decimal dqcs = 0;
                string cs = string.Format("SELECT [jcnum] FROM [csmx] where cardID='{0}' and jcxmname='{1}'", txtcardID.Text, this.dataGridView2.Rows[i].Cells[0].Value.ToString());
                SqlConnection conncs = new SqlConnection(constr);
                conncs.Open();
                SqlCommand commcs = new SqlCommand(cs,conncs);
                SqlDataReader sdr = commcs.ExecuteReader();
                while (sdr.Read())
                {
                    sycs =decimal.Parse( sdr["jcnum"].ToString());

                }
                //修改剩余次数
                string csss = this.dataGridView2.Rows[i].Cells[1].Value.ToString();
                dqcs = sycs - decimal.Parse(csss);
                if (dqcs >= 0)
                {
                    //插入红虫单据
                    SqlConnection conn = new SqlConnection(constr);
                    conn.Open();
                    string sql = string.Format("INSERT INTO [计次充值]([单号],[卡号],[持卡人],[时间] ,[单据金额] ,[优惠金额] ,[应收金额],[实收金额],[找零],[经手人] ,[收款账户],[班组],if1) VALUES('{0}' ,'{1}' ,'{2}' ,'{3}','{4}' ,'{5}' ,'{6}','{7}' ,'{8}' ,'{9}' ,'{10}','{11}',0)", txtdjh.Text, txtcardID.Text, lbckr.Text, time, txtdjje.Text, txtyhje.Text, txtysje.Text, txtssje.Text, txtzl.Text, chuanzhi.userID, comboBox1.Text, chuanzhi.banzu);
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.ExecuteNonQuery();
                    SqlConnection conn2 = new SqlConnection(constr);
                    conn2.Open();
                    string sql2 = string.Format("INSERT INTO [项目充值明细]([cardid] ,[持卡人],[项目名称],[充值次数] ,[项目单价] ,[项目金额],[经手人] ,[充值时间] ,[单号],[备注],if1) VALUES  ('{0}' ,'{1}' ,'{2}' ,{3} ,{4} ,{5} ,'{6}' ,'{7}' ,'{8}','{9}',0)", txtcardID.Text, lbckr.Text, dataGridView2.Rows[i].Cells[0].Value, dataGridView2.Rows[i].Cells[1].Value, dataGridView2.Rows[i].Cells[2].Value, dataGridView2.Rows[i].Cells[3].Value, chuanzhi.userID, time, txtdjh.Text, "红冲单据");
                    SqlCommand comm2 = new SqlCommand(sql2, conn2);
                    comm2.ExecuteNonQuery();
                    SqlConnection connu = new SqlConnection(constr);
                    connu.Open();
                    //修改次数
                    string sqlu = string.Format("UPDATE [csmx] SET [jcnum] ={0}  WHERE jcxmname='{1}' and cardID='{2}' and 备注='{3}'", dqcs, this.dataGridView2.Rows[i].Cells[0].Value.ToString(), txtcardID.Text, dataGridView2.Rows[i].Cells[4].Value);

                    SqlCommand commu = new SqlCommand(sqlu, connu);
                    commu.ExecuteNonQuery();
                    scsrmx();
                    connu.Close();
                    conncs.Close();
                    hcjf();
                    MessageBox.Show("删除成功");
                    
                  chuanzhi cz = new chuanzhi();
                    cz.rizhi("卡号为："+txtcardID.Text+"充值作废单据号为"+txtdjh.Text);
                    this.Close();
                }
                else
                {
                    MessageBox.Show("当前剩余次数不足！不可删除此单据");
                }
            }
            //string cs =string.Format( "SELECT [jcnum] FROM [csmx] where cardID='{0}' and jcxmname='{1}'",txtcardID.Text,);
           // MessageBox.Show("删除单据成功，请到会员管理手动修正次数");
           
        }
        public void hcjf()
        {
            //先用现有的积分  减去增加的积分
            decimal jf_upjf = jf_dqjf - jf_zjjf + jf_kcjf;
            SqlConnection con_ipjf = new SqlConnection(constr);
            con_ipjf.Open();
            string sql_upjf =string.Format("UPDATE member SET [积分] = '{0}',[累计积分]='{0}' WHERE mid='{1}'", jf_upjf,txtcardID.Text);
            SqlCommand com_upjf = new SqlCommand(sql_upjf,con_ipjf);
            com_upjf.ExecuteNonQuery();
            con_ipjf.Close();
        }
        public void scsrmx()
        {
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            string sql =string.Format( "DELETE FROM [收入明细] WHERE djh='{0}'",txtdjh.Text);
            SqlCommand comm = new SqlCommand(sql,conn);
            comm.ExecuteNonQuery();
            conn.Close();
        }
        private void dataGridView2_UserDeletedRow(object sender, DataGridViewRowEventArgs e)
        {
            DataGridView dgv = dataGridView2;
            Total(dgv);
            txtysje.Text = (decimal.Parse(txtdjje.Text) - decimal.Parse(txtyhje.Text)).ToString();
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (cbjf.Checked == true)
            {
                lbkc1.Visible = true;
                lbkc2.Visible = true;
                lbkc3.Visible = true;
                txtkcjf.Visible = true;
                txtdkje.Visible = true;
            }
            else
            {
                lbkc1.Visible = false;
                lbkc2.Visible = false;
                lbkc3.Visible = false;
                txtkcjf.Visible = false;
                txtdkje.Visible = false;
            }
        }

        private void txtdkje_TextChanged(object sender, EventArgs e)
        {
            try
            {
                txtysje.Text = (decimal.Parse(txtdjje.Text) - decimal.Parse(txtyhje.Text) - decimal.Parse(txtdkje.Text)).ToString();
            }
            catch { }
        }



        /* private void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
         {
             this.dataGridView2.SelectedCells[3].Value = decimal.Parse(this.dataGridView2.SelectedCells[5].Value.ToString()) / decimal.Parse(this.dataGridView2.SelectedCells[4].Value.ToString());
         }
         */
    }
}
